#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi


DWD_CLEANED_WIRELESS_DATA="
INSERT INTO TABLE 09_shop_db.cleaned_wireless_data PARTITION (dt)
SELECT
    id,
    CASE WHEN user_id IS NULL OR user_id = '' THEN 'unknown_user' ELSE user_id END,
    CASE WHEN from_unixtime(unix_timestamp(visit_time)) IS NULL THEN '1970-01-01 00:00:00' ELSE visit_time END,
    COALESCE(entry_point, 'unknown_entry'),
    COALESCE(page_type, 'other_page'),
    COALESCE(page_url, 'unknown_url'),
    COALESCE(referrer_page, 'direct'),
    COALESCE(next_page, 'exit'),
    CASE WHEN page_view_count < 1 THEN 1 WHEN page_view_count > 100 THEN 100 ELSE page_view_count END,
    unique_visitor_count,
    CASE WHEN stay_duration < 0 THEN 0 WHEN stay_duration > 3600 THEN 3600 ELSE stay_duration END,
    CASE WHEN is_order NOT IN ('0', '1') THEN '0' ELSE is_order END,
    CASE WHEN is_order = '1' AND (order_amount IS NULL OR order_amount < 0) THEN 0.0
         WHEN is_order = '1' AND order_amount > 10000 THEN 10000.0
         ELSE order_amount END,
    dt
FROM 09_shop_db.wireless_data
WHERE dt = '2025-01-01';
"

DWD_CLEANED_PC_DATA="
INSERT INTO TABLE 09_shop_db.cleaned_pc_data PARTITION (dt)
SELECT
    id,
    CASE WHEN user_id IS NULL OR user_id = '' THEN 'unknown_user' ELSE user_id END,
    CASE WHEN from_unixtime(unix_timestamp(visit_time)) IS NULL THEN '1970-01-01 00:00:00' ELSE visit_time END,
    COALESCE(traffic_entry, 'unknown_entry'),
    COALESCE(page_type, 'other_page'),
    COALESCE(page_url, 'unknown_url'),
    COALESCE(referrer_page, 'direct'),
    COALESCE(next_page, 'exit'),
    CASE WHEN page_view_count < 1 THEN 1 WHEN page_view_count > 100 THEN 100 ELSE page_view_count END,
    unique_visitor_count,
    CASE WHEN stay_duration < 0 THEN 0 WHEN stay_duration > 3600 THEN 3600 ELSE stay_duration END,
    CASE WHEN is_order NOT IN ('0', '1') THEN '0' ELSE is_order END,
    CASE WHEN is_order = '1' AND (order_amount IS NULL OR order_amount < 0) THEN 0.0
         WHEN is_order = '1' AND order_amount > 10000 THEN 10000.0
         ELSE order_amount END,
    dt
FROM 09_shop_db.pc_data
WHERE dt = '2025-01-01';
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${DWD_CLEANED_WIRELESS_DATA}${DWD_CLEANED_PC_DATA}"